from sqlalchemy import Column, Integer, String, Float, Text, DateTime , ForeignKey
from sqlalchemy.orm import relationship

from .database import Base

class Catogories(Base):
    """题目类别"""
    __tablename__ = 'catogories'

    id = Column(Integer, nullable=False, primary_key=True, comment='标识符')
    name = Column(String(45), nullable=False, comment='名称')
    description = Column(Text, nullable=True, comment='描述')

    scores = relationship("Scores", back_populates='owner_catogories')
    questions = relationship('Questions', back_populates='owner_catogories')

class Classes(Base):
    """班级"""
    __tablename__ = 'classes'

    id = Column(Integer, nullable=False, primary_key=True, comment='标识符')
    name = Column(String(15), nullable=False, unique=True, comment='班级名')
    enabled = Column(Integer, nullable=False, comment='是否启用')

    user_class = relationship('User_Class', back_populates='owner_classes')
    contest_classes = relationship('Contest_Class', back_populates='owner_classes')

class Users(Base):
    """用户"""
    __tablename__ = 'users'

    id = Column(Integer, nullable=False, primary_key=True, comment='标识符')
    number = Column(String(12), nullable=False, unique=True, comment='学号')
    password = Column(String(60), nullable=False, comment='密码')
    name = Column(String(10), nullable=True, comment='姓名')
    sex = Column(Integer, nullable=True, comment='性别')
    enabled = Column(Integer, nullable=False, comment='用户是否启用', default=1)
    role = Column(String(5), nullable=False, comment='角色', default='学生')

    tests = relationship('Tests', back_populates='owner_users')
    scores = relationship('Scores', back_populates='owner_users')
    user_class = relationship('User_Class', back_populates='owner_users')

class User_Class(Base):
    """用户班级表"""
    """为解决教师有多个教学班"""
    __tablename__ = 'user_class'

    id = Column(Integer, nullable=False, primary_key=True, comment='标识符')
    users_id = Column(Integer, ForeignKey(Users.id) ,nullable=False, comment='用户标识符')
    classes_id = Column(Integer, ForeignKey(Classes.id), nullable=False, comment='班级标识符')

    owner_users = relationship('Users', back_populates='user_class')
    owner_classes = relationship('Classes', back_populates='user_class')

class Scores(Base):
    """分数"""
    __tablename__ = 'scores'

    id = Column(Integer, nullable=False, primary_key=True, comment='标识符')
    users_id = Column(Integer, ForeignKey(Users.id), nullable=False, comment='用户标识符')
    catogories_id = Column(Integer, ForeignKey(Catogories.id), nullable=False, comment='类别标识符')
    usual_score = Column(Float, nullable=True, comment='平时成绩')

    owner_catogories = relationship('Catogories', back_populates='scores')
    owner_users = relationship('Users', back_populates='scores')

class Contests(Base):
    """竞赛表"""
    __tablename__ = 'contests'

    id = Column(Integer, nullable=False, primary_key=True, comment='标识符')
    contest_id = Column(Integer, nullable=False, comment='竞赛id')
    start_time = Column(DateTime, nullable=False, comment='竞赛起始时间')
    end_time = Column(DateTime, nullable=False, comment='竞赛结束时间')

    contest_questions = relationship('Contest_Questions', back_populates='owner_contests')
    contest_classes = relationship('Contest_Class', back_populates='owner_contests')

class Questions(Base):
    """问题表"""
    __tablename__ = 'questions'

    id = Column(Integer, nullable=False, primary_key=True, comment='标识符')
    name = Column(String(45), nullable=False, comment='问题名')
    description = Column(Text, nullable=True, comment='描述')
    question_id = Column(Integer, nullable=True, comment='题号')
    catogories_id = Column(Integer, ForeignKey(Catogories.id), nullable=False, comment='类别标识符')

    tests = relationship('Tests', back_populates='owner_questions')
    test_data = relationship('Test_data', back_populates='owner_questions')
    contest_questions = relationship('Contest_Questions', back_populates='owner_questions')

    owner_catogories = relationship('Catogories', back_populates='questions')

class Contest_Questions(Base):
    """竞赛问题表"""
    """为解决一个竞赛有多个问题"""
    __tablename__ = 'contest_question'

    id = Column(Integer, nullable=False, primary_key=True, comment='标识符')
    contest_id = Column(Integer, ForeignKey(Contests.id), nullable=False, comment='竞赛标识符')
    question_id = Column(Integer, ForeignKey(Questions.id), nullable=False, comment='问题标识符')

    owner_contests = relationship('Contests', back_populates='contest_questions')
    owner_questions = relationship('Questions', back_populates='contest_questions')

class Contest_Class(Base):
    """竞赛班级表"""
    """为解决一个竞赛有多个班级，或一个班级有多个竞赛"""
    __tablename__ = 'contest_class'

    id = Column(Integer, nullable=False, primary_key=True, comment='标识符')
    contest_id = Column(Integer, ForeignKey(Contests.id), nullable=False, comment='竞赛标识符')
    class_id = Column(Integer, ForeignKey(Classes.id), nullable=False, comment='班级标识符')

    owner_contests = relationship('Contests', back_populates='contest_classes')
    owner_classes = relationship('Classes', back_populates='contest_classes')

class Tests(Base):
    """测试表"""
    __tablename__ = 'tests'

    id = Column(Integer, nullable=False, primary_key=True, comment='标识符')
    submit_time = Column(DateTime, nullable=False, comment='提交时间')
    code = Column(Text, nullable=True, comment='代码')
    correct_rate = Column(Float, nullable=False, comment='正确率')
    users_id = Column(Integer, ForeignKey(Users.id) , nullable=False, comment='用户标识符')
    questions_id = Column(Integer, ForeignKey(Questions.id) , nullable=False, comment='题号')

    owner_users = relationship("Users", back_populates='tests')
    owner_questions = relationship('Questions', back_populates='tests')

class Advisors(Base):
    """指导表"""
    __tablename__ = 'advisors'

    id = Column(Integer, nullable=False, primary_key=True, comment='标识符')
    users_id_user = Column(Integer, ForeignKey(Users.id), nullable=False, comment='被指导者')
    users_id_admin = Column(Integer, ForeignKey(Users.id), nullable=False, comment='指导者')

    owner_users_user = relationship('Users', foreign_keys=[users_id_user])
    owner_users_admin = relationship('Users', foreign_keys=[users_id_admin])

class Test_data(Base):
    """测试数据表"""
    __tablename__ = 'test_data'

    id = Column(Integer, nullable=False, primary_key=True, comment='标识符')
    questions_id = Column(Integer, ForeignKey(Questions.id), nullable=False, comment='题号')
    input = Column(Text, nullable=False, comment='一组输入')
    output = Column(Text, nullable=False, comment='一组输出')

    owner_questions = relationship('Questions', back_populates='test_data')